/**
 *
 */
package com.storm.lib.util;

import java.util.ArrayList;
import java.util.List;


import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

import com.storm.lib.bean.DataCellBean;
import com.storm.lib.bean.DataColumnRuleBean;
import com.storm.lib.bean.DataRowBean;
import com.storm.lib.bean.ReplaceBean;
import com.storm.lib.bean.SheetBean;
import com.storm.lib.exception.InvalidParamException;
import com.storm.lib.type.ExcelType;


/**
 * 类功能: Excel的工具类
 * @author johnny
 * @version 2010-10-27
 */
public class ExcelUtil {
	protected final static Logger logger = Logger.getLogger(ExcelUtil.class);
	/**
	 * 方法功能:从一个Sheet得到SheetBean数据
	 * 更新时间:2010-10-27, 作者:johnny
	 * @param sheet
	 * @return
	 */
	public static SheetBean getSheetBeanBySheet(HSSFSheet sheet,String language) {
		SheetBean sheetBean = new SheetBean();

		HSSFRow row = null;
		HSSFCell cell =null;
		for (int rowNumber = 1; rowNumber <=sheet.getLastRowNum(); rowNumber++) {
			row = sheet.getRow(rowNumber);
			if(null==row){
				continue;
			}
			cell = row.getCell(0);
		}
		List<Object> parameters = getSheetParamList(sheet);
		List<String> columns = getSheetColumnsList(sheet);
		List<DataColumnRuleBean> dataColumnRuleBeanList = getSheetDataColumnRuleBeanListt(sheet);
		List<DataRowBean> rows = getSheetDataRows(sheet,columns,dataColumnRuleBeanList);

		List<Integer> abandomLanguageColumnIndex= getAbandomLanguageColumnIndexs(sheet,language);
		List<String> sampleColumns = getSheetSampleColumnsList(sheet,abandomLanguageColumnIndex);
		List<DataColumnRuleBean> sampleColumnRuleBeanList = getSheetSampleDataColumnRuleBeanListt(sheet,abandomLanguageColumnIndex);
		List<DataRowBean> sampleDatas = getSheetSampleDataRows(sheet,sampleColumns,sampleColumnRuleBeanList,abandomLanguageColumnIndex);

		sheetBean.setParameters(parameters);
		sheetBean.setColumns(columns);
		sheetBean.setRows(rows);
		sheetBean.setSampleColumns(sampleColumns);
		sheetBean.setSampleDatas(sampleDatas);
		return sheetBean;
	}


	private static List<Integer> getAbandomLanguageColumnIndexs(HSSFSheet sheet,String language) {
		List<Integer> index = new ArrayList<Integer>();
		Integer sampleTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_SAMPLE);
		//没有找到数据行标示
		if(sampleTagRowIndex==null){
			return null;
//			ExceptionUtil.throwException(new InvalidParamException());
		}
		HSSFRow columnRow = sheet.getRow(sampleTagRowIndex+2);
		//字段行不存在
		if(columnRow==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		for (int i = 0; i <= columnRow.getLastCellNum(); i++) {
			HSSFCell cell = columnRow.getCell(i);
			String obj=(String)convertCellToObjectByExcelDefault(cell);
			if(obj==null){
				LogUtil.warm("列明存在列数"+i+"为空值");
			}
			else{
				if(obj.startsWith("#")){
					if(false==obj.split("#")[1].equals(language)){
						index.add(i);
					}
				}
			}
		}
		return index;
	}







	/**
	 *
	 * 更新时间:2010-10-28, 作者:johnny
	 * 方法功能:根据规则获得数据
	 * @param sheet
	 * @param columns
	 * @param dataColumnRuleBeanList
	 * @return
	 */
	private static List<DataRowBean> getSheetDataRows(HSSFSheet sheet,List<String> columns,List<DataColumnRuleBean> dataColumnRuleBeanList) {
		List<DataRowBean> dataRowBeanList = new ArrayList<DataRowBean>();
		Integer dataTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_DATA);
		Integer sampleTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_SAMPLE);
		//没有找到数据行标示
		if(dataTagRowIndex==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		int finalDataRow = sheet.getLastRowNum();
		if(sampleTagRowIndex!=null){
			finalDataRow = sampleTagRowIndex-1;
		}
		int firstDataRow = dataTagRowIndex+3;
		//数据行不存在
		if(firstDataRow>finalDataRow){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		row: for (int dataRowIndex = firstDataRow; dataRowIndex <= finalDataRow; dataRowIndex++) {
			logger.debug("dataRowIndex: "+ dataRowIndex);
			HSSFRow dataRow = sheet.getRow(dataRowIndex);
			DataRowBean dataRowBean = new DataRowBean();
			dataRowBean.setDataRowIndex(firstDataRow-dataRowIndex+1);
			List<DataCellBean> dataCellList = new ArrayList<DataCellBean>();
			for (int dataCellIndex = 0; dataCellIndex <= dataRow.getLastCellNum(); dataCellIndex++) {
				logger.debug("dataCellIndex: "+ dataCellIndex);
				DataCellBean dataCellBean = new DataCellBean();
				DataColumnRuleBean dataColumnRuleBean  = dataColumnRuleBeanList.get(dataCellIndex);
				if(dataColumnRuleBean==null){
					dataCellList.add(null);
					continue;
				}
				HSSFCell cell = dataRow.getCell(dataCellIndex);
				System.out.println(cell);
				Object obj=null;
				if(dataColumnRuleBean.getNotNone()==true){
					if(getCellObjectByTypePrefix(cell, dataColumnRuleBean.getTypePrefix())==null){
						continue row;
					}
				}
				//类型转换
				obj = getCellObjectByTypePrefix(cell, dataColumnRuleBean.getTypePrefix());
				if(dataColumnRuleBean.getReplaceList()==null){

					//替换空值
					if(obj==null && dataColumnRuleBean.getNullValue()!=null){
						obj = getObjectByTypePrefix(dataColumnRuleBean.getTypePrefix(),dataColumnRuleBean.getNullValue());
					}
				}
				else{
					if(obj==null){
						//替换指定值
						for (int i = 0; i < dataColumnRuleBean.getReplaceList().size(); i++) {
							ReplaceBean replaceBean = dataColumnRuleBean.getReplaceList().get(i);
							if(checkReplaceConditionReach(replaceBean.getConditionColumnName(),replaceBean.getConditionValue(),dataRow,columns)){
								obj = getObjectByTypePrefix(dataColumnRuleBean.getTypePrefix(), dataColumnRuleBean.getReplaceList().get(i).getReplaceValue());
							}
						}
					}
				}
				dataCellBean.setData(obj);
				dataCellBean.setDataCellIndex(dataCellIndex);
				dataCellBean.setDataRowIndex(dataRowBean.getDataRowIndex());
				dataCellList.add(dataCellBean);
			}
			dataRowBean.setDataCellList(dataCellList);
			dataRowBeanList.add(dataRowBean);
		}
		return dataRowBeanList;
	}

	/**
	 * 方法功能:根据规则获得范例数据
	 * 更新时间:2010-12-21, 作者:johnny
	 * @param sheet
	 * @param sampleColumns
	 * @param dataColumnRuleBeanList
	 * @return
	 */
	private static List<DataRowBean> getSheetSampleDataRows(HSSFSheet sheet,List<String> sampleColumns,List<DataColumnRuleBean> dataColumnRuleBeanList,List<Integer> abandomLanguageColumnIndexs) {
		List<DataRowBean> dataRowBeanList = new ArrayList<DataRowBean>();
		Integer sampleDataTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_SAMPLE);
		//没有找到数据行标示
		if(sampleDataTagRowIndex==null){
			return null;
//			ExceptionUtil.throwException(new InvalidParamException());
		}
		int finalDataRow = sheet.getLastRowNum();
		int firstDataRow = sampleDataTagRowIndex+3+1;
		//数据行不存在
		if(firstDataRow>finalDataRow){
			return new ArrayList<DataRowBean>();
//			ExceptionUtil.throwException(new InvalidParamException());
		}
		row: for (int dataRowIndex = firstDataRow; dataRowIndex <= finalDataRow; dataRowIndex++) {
			logger.debug("当前行"+dataRowIndex);
			HSSFRow dataRow = sheet.getRow(dataRowIndex);
			DataRowBean dataRowBean = new DataRowBean();
			dataRowBean.setDataRowIndex(firstDataRow-dataRowIndex+1);
			List<DataCellBean> dataCellList = new ArrayList<DataCellBean>();
			//由于多语言的存在,RuleBean的长度和数据不一致
			int currentRuleBeanIndex=0;
			 cell: for (int dataCellIndex = 0; dataCellIndex < dataRow.getLastCellNum(); dataCellIndex++) {
				if(ArrayUtil.intInArray(dataCellIndex, abandomLanguageColumnIndexs)){
					continue;
				}
				System.out.println("当前列"+dataCellIndex);
				DataCellBean dataCellBean = new DataCellBean();
				//如果这里越界报错,就说明数据sample行超出了列
				DataColumnRuleBean dataColumnRuleBean  = dataColumnRuleBeanList.get(currentRuleBeanIndex++);
				if(dataColumnRuleBean==null){
					dataCellList.add(null);
					continue;
				}
				HSSFCell cell = dataRow.getCell(dataCellIndex);
				Object obj=null;
				if(dataColumnRuleBean.getNotNone()==true){
					if(getCellObjectByTypePrefix(cell, dataColumnRuleBean.getTypePrefix())==null){
						continue row;
					}
				}
				//类型转换
				obj = getCellObjectByTypePrefix(cell, dataColumnRuleBean.getTypePrefix());
				if(dataColumnRuleBean.getReplaceList()==null){

					//替换空值
					if(obj==null && dataColumnRuleBean.getNullValue()!=null){
						obj = getObjectByTypePrefix(dataColumnRuleBean.getTypePrefix(),dataColumnRuleBean.getNullValue());
					}
				}
				else{
					if(obj==null){
						//替换指定值
						for (int i = 0; i < dataColumnRuleBean.getReplaceList().size(); i++) {
							ReplaceBean replaceBean = dataColumnRuleBean.getReplaceList().get(i);
							if(checkReplaceConditionReach(replaceBean.getConditionColumnName(),replaceBean.getConditionValue(),dataRow,sampleColumns)){
								obj = getObjectByTypePrefix(dataColumnRuleBean.getTypePrefix(), dataColumnRuleBean.getReplaceList().get(i).getReplaceValue());
							}
						}
					}
				}
				dataCellBean.setData(obj);
				dataCellBean.setDataCellIndex(dataCellIndex);
				dataCellBean.setDataRowIndex(dataRowBean.getDataRowIndex());
				dataCellList.add(dataCellBean);
			}
			dataRowBean.setDataCellList(dataCellList);
			dataRowBeanList.add(dataRowBean);
		}
		return dataRowBeanList;
	}

	/**
	 *
	 * 更新时间:2010-10-28, 作者:johnny
	 * 方法功能: 判断是否存在指定Column值相当的情况
	 * @param conditionColumnName
	 * @param conditionValue
	 * @param dataRow
	 * @param columns
	 * @return
	 */
	private static boolean checkReplaceConditionReach(String conditionColumnName, String conditionValue, HSSFRow dataRow,List<String> columns) {
		for (int i = 0; i < columns.size(); i++) {
			if(conditionColumnName.equals(columns.get(i))){
				HSSFCell cell = dataRow.getCell(i);
				if(conditionValue.equals(getCellObjectByTypePrefix(cell, ExcelType.STRING_TYPE_PREFIX))){
					return true;
				}
			}
		}
		return false;
	}

	/**
	 *
	 * 更新时间:2010-10-27, 作者:johnny
	 * 方法功能:得到规则列表
	 * @param sheet
	 * @return
	 */
	private static List<DataColumnRuleBean> getSheetDataColumnRuleBeanListt(HSSFSheet sheet) {
		List<DataColumnRuleBean> dataColumnRuleBeanList = new ArrayList<DataColumnRuleBean>();
		Integer dataTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_DATA);
		//没有找到数据行标示
		if(dataTagRowIndex==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		HSSFRow columnRuleRow = sheet.getRow(dataTagRowIndex+1);
		//规则行不存在
		if(columnRuleRow==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		for (int i = 0; i <= columnRuleRow.getLastCellNum(); i++) {
			HSSFCell cell = columnRuleRow.getCell(i);
			if(cell==null){
				dataColumnRuleBeanList.add(null);
				continue;
			}
			String expressionStr=(String)convertCellToObjectByExcelDefault(cell);
			if(expressionStr==null){
				continue;
			}
			DataColumnRuleBean dataColumnRuleBean = getDataColumnRuleBeanByExpressionStr(expressionStr);
			dataColumnRuleBeanList.add(dataColumnRuleBean);
		}
		return dataColumnRuleBeanList;
	}

	/**
	 * 方法功能:得到范例行规则列表
	 * 更新时间:2010-12-21, 作者:johnny
	 * @param sheet
	 * @return
	 */
	private static List<DataColumnRuleBean> getSheetSampleDataColumnRuleBeanListt(HSSFSheet sheet,List<Integer> abandomLanguageColumnIndexs) {
		List<DataColumnRuleBean> dataColumnRuleBeanList = new ArrayList<DataColumnRuleBean>();
		Integer dataTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_SAMPLE);
		//没有找到数据行标示
		if(dataTagRowIndex==null){
			return null;
//			ExceptionUtil.throwException(new InvalidParamException());
		}
		HSSFRow columnRuleRow = sheet.getRow(dataTagRowIndex+1);
		//规则行不存在
		if(columnRuleRow==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		for (int i = 0; i <= columnRuleRow.getLastCellNum(); i++) {
			if(ArrayUtil.intInArray(i, abandomLanguageColumnIndexs)){
				continue;
			}
			HSSFCell cell = columnRuleRow.getCell(i);
			if(cell==null){
				//dataColumnRuleBeanList.add(null);
				continue;
			}
			String expressionStr=(String)convertCellToObjectByExcelDefault(cell);
			if(expressionStr==null){
				continue;
			}
			DataColumnRuleBean dataColumnRuleBean = getDataColumnRuleBeanByExpressionStr(expressionStr);
			dataColumnRuleBeanList.add(dataColumnRuleBean);
		}
		return dataColumnRuleBeanList;
	}

	/**
	 *
	 * 更新时间:2010-10-27, 作者:johnny
	 * 方法功能: 返回拼装好的规则Bean
	 * @param expressionStr 例如i,#类型==TINYINT:1,#类型==BIGINT:20,null=false
	 * @return
	 */
	private static DataColumnRuleBean getDataColumnRuleBeanByExpressionStr(String expressionStr) {
		DataColumnRuleBean dataColumnRuleBean = new DataColumnRuleBean();
		if(expressionStr==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		String[] values = expressionStr.split(",");
		//没有基本项
		if(values.length<1){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		dataColumnRuleBean.setTypePrefix(values[0]);
		for (int i = 1; i < values.length; i++) {
			if(values[i].startsWith("null=")){
				dataColumnRuleBean.setNullValue(getObjectByTypePrefix(dataColumnRuleBean.getTypePrefix(),values[i].replace("null=", "")));
			}
			if(values[i].startsWith("#") && false==values[i].startsWith("#NOTNULL")){
				dataColumnRuleBean.addReplaceBeanByStr(values[i]);
			}
			if(values[i].startsWith("#NOTNULL")){
				dataColumnRuleBean.setNotNone(true);
			}
		}
		return dataColumnRuleBean;
	}

	/**
	 *
	 * 更新时间:2010-10-27, 作者:johnny
	 * 方法功能:获得Sheet列名列表
	 * @param sheet
	 * @return
	 */
	private static List<String> getSheetColumnsList(HSSFSheet sheet) {
		List<String> columnList =new ArrayList<String>();
		Integer dataTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_DATA);
		//没有找到数据行标示
		if(dataTagRowIndex==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		HSSFRow columnRow = sheet.getRow(dataTagRowIndex+2);
		//字段行不存在
		if(columnRow==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		for (int i = 0; i <= columnRow.getLastCellNum(); i++) {
			HSSFCell cell = columnRow.getCell(i);
			String obj=(String)convertCellToObjectByExcelDefault(cell);
			if(obj==null){
				LogUtil.warm("列明存在列数"+i+"为空值");
			}
			columnList.add(obj);
		}
		return columnList;
	}

	/**
	 * 方法功能:获得Sheet范例数据列名列表
	 * 更新时间:2010-12-21, 作者:johnny
	 * @param sheet
	 * @return
	 */
	private static List<String> getSheetSampleColumnsList(HSSFSheet sheet,List<Integer> abandomLanguageColumnIndexs) {
		List<String> columnList =new ArrayList<String>();
		Integer sampleTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_SAMPLE);
		//没有找到数据行标示
		if(sampleTagRowIndex==null){
			return null;
//			ExceptionUtil.throwException(new InvalidParamException());
		}
		HSSFRow columnRow = sheet.getRow(sampleTagRowIndex+2);
		//字段行不存在
		if(columnRow==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		for (int i = 0; i <= columnRow.getLastCellNum(); i++) {
			HSSFCell cell = columnRow.getCell(i);
			String obj=(String)convertCellToObjectByExcelDefault(cell);
			if(obj==null){
				LogUtil.warm("列明存在列数"+i+"为空值");
				continue;
			}
			else{
				if(ArrayUtil.intInArray(i, abandomLanguageColumnIndexs)){
					continue;
				}
				if(obj.startsWith("#")){
					obj=obj.split("#")[2];
				}
			}

			columnList.add(obj);
		}
		return columnList;
	}

	/**
	 * 方法功能:得到参数列表
	 * 更新时间:2010-10-27, 作者:johnny
	 * @param sheet
	 * @return
	 */
	private static List<Object> getSheetParamList(HSSFSheet sheet) {
		List<Object> paramList =new ArrayList<Object>();
		Integer metaTagRowIndex = getTagRowByName(sheet,ExcelType.TAG_NAME_META_DATA);
		//没有找到元参数行
		if(metaTagRowIndex==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		HSSFRow paramRow = sheet.getRow(metaTagRowIndex+1);
		//参数行不存在
		if(paramRow==null){
			ExceptionUtil.throwException(new InvalidParamException());
		}
		String typePrefix=null;
		int num = paramRow.getLastCellNum();
		for (int i = 0; i <= num; i++) {
			HSSFCell cell = paramRow.getCell(i);
			if(ExceptionUtil.isBlankOrNullCell(cell)){
				continue;
			}
			if(i%2==0){
				typePrefix=(String)convertCellToObjectByExcelDefault(cell);
			}
			if(i % 2 != 0){
				if(typePrefix==null){
					ExceptionUtil.throwException(new InvalidParamException());
				}
				Object obj = getCellObjectByTypePrefix(cell,typePrefix);
				paramList.add(obj);
			}
		}
		return paramList;
	}

	/**
	 *
	 * 更新时间:2010-10-27, 作者:johnny
	 * 方法功能:根据指定前缀强行类型并返回Cell，例如i=Integer s=String
	 * @param cell
	 * @param typePrefix
	 * @return 指定类型
	 */
	private static Object getCellObjectByTypePrefix(HSSFCell cell, String typePrefix) {
		Object obj = convertCellToObjectByExcelDefault(cell);
		if(obj==null){
			return null;
		}
		obj = getObjectByTypePrefix(typePrefix, obj);
		return obj;
	}

	/**
	 *
	 * 更新时间:2010-10-27, 作者:johnny
	 * 方法功能:根据指定前缀强行类型，例如i=Integer s=String
	 * @param cell
	 * @param typePrefix
	 * @return 指定类型
	 */
	private static Object getObjectByTypePrefix(String typePrefix, Object obj) {
		try {
			if (ExcelType.INTEGER_TYPE_PREFIX.equals(typePrefix)) {
				if (obj instanceof Integer) {
					return (Integer) obj;
				}
				if (obj instanceof Double) {
					return ((Double) obj).intValue();
				}
				if (obj instanceof String) {
					return Integer.valueOf(((String) obj));
				}
				if (obj instanceof Boolean) {
					if ((Boolean) obj == true) {
						return 1;
					} else {
						return 0;
					}
				}
			}
			if (ExcelType.STRING_TYPE_PREFIX.equals(typePrefix)) {
				if (obj instanceof Integer) {
					return ((Integer) obj).toString();
				}
				if (obj instanceof Double) {
					return ((Double) obj).toString();
				}
				if (obj instanceof String) {
					return (String) obj;
				}
				if (obj instanceof Boolean) {
					if ((Boolean) obj == true) {
						return "true";
					} else {
						return "false";
					}
				}
			}
			if (ExcelType.BOOL_TYPE_PREFIX.equals(typePrefix)) {
				if (obj instanceof Integer) {
					if ((Integer) obj == 0) {
						return true;
					} else {
						return false;
					}
				}
				if (obj instanceof Double) {
					if ((Double) obj == 0) {
						return true;
					} else {
						return false;
					}
				}
				if (obj instanceof String) {
					if (("true".equals((String) obj))) {
						return true;
					} else {
						return false;
					}
				}
				if (obj instanceof Boolean) {
					return (Boolean) obj;
				}
			}
		} catch (NumberFormatException e) {
			LogUtil.warm("强行转换类型");
			return null;
		}
		ExceptionUtil.throwException(new InvalidParamException());
		return null;
	}

	/**
	 * 方法功能:
	 * 更新时间:2010-10-27, 作者:johnny
	 * @param sheet 需要的sheet
	 * @param tagNameMetaData 参数
	 * @return 返回Excel对应的行数,没有找到匹配返回null
	 */
	public static Integer getTagRowByName(HSSFSheet sheet, String tagNameMetaData) {
		HSSFRow row = null;
		HSSFCell cell =null;
		for (int rowNumber = 0; rowNumber <=sheet.getLastRowNum(); rowNumber++) {
			row = sheet.getRow(rowNumber);
			for (int cellNumber = 0; cellNumber < row.getLastCellNum(); cellNumber++) {
				cell = row.getCell(cellNumber);
				Object object = convertCellToObjectByExcelDefault(cell);
				if(object instanceof String && tagNameMetaData.equals((String)object)){
					return rowNumber;
				}
			}
		}
		return null;
	}

	/**
	 * 方法功能:根据Excel的格式转换Object
	 * 更新时间:2010-10-27, 作者:johnny
	 * @param cell
	 * @return Double,String或者null
	 */
	public static Object convertCellToObjectByExcelDefault(HSSFCell cell) {

		if(cell==null){
			return null;
		}
		if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
			return cell.getStringCellValue().trim();
		}
		if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
			return DoubleUtil.toInt(cell.getNumericCellValue());
		}
		if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
			return cell.getBooleanCellValue();
		}
		if(cell.getCellType()==HSSFCell.CELL_TYPE_BLANK){
			return null;
		}
		if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA){
			ExceptionUtil.throwInvalidParamException("出现公示了");
		}
		ObjectUtil.printObject(cell);
		ExceptionUtil.throwException(new InvalidParamException());
		return null;
	}

}
